DotNetNuke runs a task to purge it’s schedule history; however, the stored procedure that does this has performance problems that will cause deadlocks on a high traffic website.
The query that the PurgeScheduleHistory stored procedure ships with is:
DELETE FROM dbo.ScheduleHistory
FROM dbo.Schedule s
WHERE (
SELECT COUNT(*)
FROM dbo.ScheduleHistory sh with (nolock)
WHERE sh.ScheduleID = ScheduleHistory.ScheduleID
AND sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum
AND s.RetainHistoryNum <> -1
AND s.ScheduleID = ScheduleHistory.ScheduleID
Anytime you write a query that does a delete from a select you run the risk of deadlock. Also, whenever you do a sweeping delete on a table you can escalate to a page lock, have trouble with locking indexes, and end up in a deadlock scenario again. On the surface this doesn’t sound like a deadlock scenario, but you have to understand the way locks affect indexes and how locks escalate in SQL Server—you might be surprised to learn a simple SELECT query can deadlock under the right conditions also (http://stackoverflow.com/questions/661908/sql-server-deadlocks-between-select-update-or-multiple-selects).
The fix to this stored procedure follows the same approach Microsoft used to fix the DeleteExpiredSessions stored procedure that ships with ASP.NET (http://support.microsoft.com/kb/973849).
The following query should replace the PurgeScheduleHistory stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PurgeScheduleHistory]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
create table #T (ID int not null primary key)
insert into #T
select schedulehistoryid from (
select s.ScheduleID, sh.schedulehistoryid, rank() over (partition by s.scheduleid order by sh.startdate) rn, RetainHistoryNum
from ScheduleHistory sh WITH (READUNCOMMITTED)
join Schedule s WITH (READUNCOMMITTED) on s.ScheduleID = sh.ScheduleID
where s.RetainHistoryNum <> -1) a
where rn > RetainHistoryNum
DECLARE ESC CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT ID FROM #T
declare @ID int
open ESC
fetch next from ESC into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM ScheduleHistory WHERE ScheduleHistoryID = @ID
FETCH NEXT FROM ESC INTO @ID
END
CLOSE ESC
DEALLOCATE ESC
drop table #T
Thanks to David Eison for finding this solution.
